package com.gxan.renshidao.impl;

import com.gxan.pojo.Archives;
import com.gxan.renshidao.ArchivesDao;
import com.gxan.utils.db.JdbcUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class ArchivesDaoImpl extends JdbcUtils implements ArchivesDao {
    //查询所有的Archives信息
    @Override
    public List<Archives> findAllArchives(Integer offset, Integer rows) throws SQLException {
        String sql = "SELECT DISTINCT standard_of_sal.sal_standard_name,archives.status,archives.audit_status,archives.archives_id,archives.dept_id,archives.job_id,archives.archives_type,archives.name,archives.sex,archives.email,archives.phone,archives.address,archives.identity,archives.location,archives.politic_outlook,archives.graduate_school,archives.major,archives.degree,archives.sal_standard_id,archives.bank,archives.count,archives.security_card_number,archives.regist,archives.set_time,archives.resume,archives.relationship,archives.remark,archives.resume_url,archives.head_url,sys_dept.dept_full_name,sys_job.job_name,sys_job_title.job_title_name\n" +
                "FROM archives\n" +
                "LEFT JOIN sys_dept ON archives.dept_id = sys_dept.dept_id\n" +
                "LEFT JOIN sys_job ON sys_dept.dept_id = sys_job.dept_id\n" +
                "LEFT JOIN sys_job_title ON sys_job_title.dept_id = sys_dept.dept_id\n" +
                "LEFT JOIN standard_of_sal ON archives.sal_standard_id = standard_of_sal.sal_standard_id\n" +
                "LIMIT ?,?";
        Object[] params = {offset, rows};
        System.out.println(super.queryListEntity(Archives.class,sql,params));
        return super.queryListEntity(Archives.class,sql,params);
    }
//查询数据条数
    @Override
    public Integer countArchives() throws SQLException {
        String sql = "select count(*) from archives";
        return super.findAllForInt(sql);
    }
//增加Archives信息
    @Override
    public Integer addArcives(Archives archives) throws SQLException {
        String sql = "insert into archives(status,audit_status,dept_id,job_id,archives_type,name,sex,email,phone,address,identity,location,politic_outlook,graduate_school,major,degree,sal_standard_id,bank,count,security_card_number,regist,set_time,resume,relationship,remark,resume_url,head_url) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] params = {archives.getStatus(),archives.getAudit_status(),archives.getDept_id(),archives.getJob_id() ,archives.getArchives_type(),archives.getName()
                ,archives.getSex(),archives.getEmail(),archives.getPhone(),archives.getAddress(),archives.getIdentity(),archives.getLocation(),archives.getPolitic_outlook(),archives.getGraduate_school()
                ,archives.getMajor(),archives.getDegree(),archives.getSal_standard_id(),archives.getBank(),archives.getCount(),archives.getSecurity_card_number(),archives.getRegist()
                ,archives.getSet_time(),archives.getResume(),archives.getRelationship(),archives.getRemark(),archives.getResume_url(),archives.getHead_url()};
        Integer num = super.executeUpdate(sql,params);
        return num;
    }
//按条件搜索Archives
    @Override
    public List<Archives> searchArchives(String archives_id, String audit_status, String status, String s_date,String t_date, Integer offset, Integer rows) throws SQLException {
        String sql = "SELECT archives.*, sys_dept.dept_full_name, sys_job.job_name, sys_job_title.job_title_name " +
                "FROM archives " +
                "LEFT JOIN sys_dept ON archives.dept_id = sys_dept.dept_id " +
                "LEFT JOIN sys_job ON sys_dept.dept_id = sys_job.dept_id " +
                "LEFT JOIN sys_job_title ON sys_job_title.dept_id = sys_dept.dept_id ";

        List<Object> list = new ArrayList<>();
        boolean b= false;
        if (archives_id != null && !"".equals(archives_id)) {
            sql += " where archives.archives_id = ?";
            list.add(archives_id);
            b = true;
        }
        if (audit_status != null && !"".equals(audit_status)) {
            if (b==false){
                sql +=" where archives.audit_status = ?";
            }
            else {
                sql += " and archives.audit_status = ?";
            }
            b = true;
            list.add(audit_status);
        }

        if (status != null && !"".equals(status)) {
            if (b==false){
                sql += " where archives.status = ?";
            }else {
                sql += " and archives.status = ?";
            }
            b=true;
            list.add(status);
        }

        if (s_date != null && t_date != null && !"".equals(s_date) && !"".equals(t_date)) {
            if (b==false){
                sql += " where archives.set_time BETWEEN ? AND ?";
            }else {
                sql += " and archives.set_time BETWEEN ? AND ?";
            }
            list.add(s_date);
            list.add(t_date);
        }
        sql += " LIMIT ?, ?";
        list.add(offset);
        list.add(rows);

        System.out.println(sql);

        List<Archives> list1 = super.queryListEntity(Archives.class, sql, list.toArray());

        return list1;
    }
//搜索出按条件的条数
    @Override
    public Integer countArchivesByCondition(String archives_id, String audit_status, String status, String s_date,String t_date) throws SQLException {
        String sql = "SELECT COUNT(*)\n" +
                "FROM (\n" +
                "    SELECT archives.*, sys_dept.dept_full_name, sys_job.job_name, sys_job_title.job_title_name\n" +
                "    FROM archives\n" +
                "    LEFT JOIN sys_dept ON archives.dept_id = sys_dept.dept_id\n" +
                "    LEFT JOIN sys_job ON sys_dept.dept_id = sys_job.dept_id\n" +
                "    LEFT JOIN sys_job_title ON sys_job_title.dept_id = sys_dept.dept_id\n" +
                ") AS QUERY";

        List<Object> list = new ArrayList<>();
        boolean b= false;
        System.out.println(archives_id);
        if (archives_id != null && !"".equals(archives_id)) {
            sql += " where QUERY.archives_id = ?";
            list.add(archives_id);
            b = true;
        }
        if (audit_status != null && !"".equals(audit_status)) {
            if (b==false){
                sql +=" where QUERY.audit_status = ?";
            }
            else {
                sql += " and QUERY.audit_status = ?";
            }
            b = true;
            list.add(audit_status);
        }

        if (status != null && !"".equals(status)) {
            if (b==false){
                sql += " where QUERY.status = ?";
            }else {
                sql += " and QUERY.status = ?";
            }
            b=true;
            list.add(status);
        }

        if (s_date != null && t_date != null && !"".equals(s_date) && !"".equals(t_date)) {
            if (b==false){
                sql += " where QUERY.set_time BETWEEN ? AND ?";
            }else {
                sql += " and QUERY.set_time BETWEEN ? AND ?";
            }
            list.add(s_date);
            list.add(t_date);
        }
        System.out.println(sql);
        Integer num = super.findAllForInt(sql, list.toArray());
        System.out.println(num);
        System.out.println("dao"+archives_id);
        return num;
    }
    @Override
    public Integer delArchives(Integer archives_id) throws SQLException {
        String sql = "delete from archives where archives_id=?";
        Integer num = super.executeUpdate(sql,archives_id);
        return num;
    }

//修改Archives信息

    @Override
    public Integer updateArchives(Archives archives) throws SQLException {
        String sql = "UPDATE archives\n" +
                "LEFT JOIN sys_dept ON archives.dept_id = sys_dept.dept_id\n" +
                "LEFT JOIN sys_job ON sys_dept.dept_id = sys_job.dept_id\n" +
                "LEFT JOIN sys_job_title ON sys_job_title.dept_id = sys_dept.dept_id\n" +
                "SET\n" +
                "    archives.status = ?,\n" +
                "    archives.audit_status = ?,\n" +
                "    archives.archives_type = ?,\n" +
                "    archives.name = ?,\n" +
                "    archives.sex = ?,\n" +
                "    archives.email = ?,\n" +
                "    archives.phone = ?,\n" +
                "    archives.address = ?,\n" +
                "    archives.identity = ?,\n" +
                "    archives.location = ?,\n" +
                "    archives.politic_outlook = ?,\n" +
                "    archives.graduate_school = ?,\n" +
                "    archives.major = ?,\n" +
                "    archives.degree = ?,\n" +
                "    archives.sal_standard_id = ?,\n" +
                "    archives.bank = ?,\n" +
                "    archives.count = ?,\n" +
                "    archives.security_card_number = ?,\n" +
                "    archives.regist = ?,\n" +
                "    archives.set_time = ?,\n" +
                "    archives.resume = ?,\n" +
                "    archives.relationship = ?,\n" +
                "    archives.remark = ?,\n" +
                "    archives.resume_url = ?,\n" +
                "    archives.head_url = ?,\n" +
                "    sys_dept.dept_full_name = ?,\n" +
                "    sys_job.job_name = ?\n"+
                 "  where archives.archives_id = ?\n";
        Object[] params = {archives.getStatus(), archives.getAudit_status(),archives.getArchives_type(),
                archives.getName(), archives.getSex(), archives.getEmail(), archives.getPhone(),
                archives.getAddress(), archives.getIdentity(), archives.getLocation(),
                archives.getPolitic_outlook(), archives.getGraduate_school(), archives.getMajor(),
                archives.getDegree(), archives.getSal_standard_id(), archives.getBank(),
                archives.getCount(), archives.getSecurity_card_number(), archives.getRegist(),
                archives.getSet_time(), archives.getResume(), archives.getRelationship(),
                archives.getRemark(), archives.getResume_url(), archives.getHead_url(),
                archives.getDept_full_name(), archives.getJob_name(),archives.getArchives_id()};
        System.out.println(Arrays.toString(params));
        System.out.println(sql);
        return super.executeUpdate(sql,params);
    }
}
